﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections;
using BO = Viewer.BusinessObject.BusinessEntities;
using System.Collections.Generic;
namespace Viewer.BusinessObject.BusinessEntities
{
    public partial class NSNhanVien : BusinessBase
    {
        NSTrinhDoTinHoc trinhDoTinHoc = new NSTrinhDoTinHoc();
        NSQuanLiNhaNuoc quanLiNN = new NSQuanLiNhaNuoc();
        NSLiLuanChinhTri liLuan = new NSLiLuanChinhTri();
        NSBangCap bangCap = new NSBangCap();
        NSTrangThaiLamViec trangThaiLamViec = new NSTrangThaiLamViec();
        NSTrinhDoChuyenMon trinhDoChuyenMonItem = new NSTrinhDoChuyenMon();
        NSQuaTrinhGianDoanBaoHiem baoHiem = new NSQuaTrinhGianDoanBaoHiem();
        NSNghiPhep nghiPhep = new NSNghiPhep();
        NSNgheNghiep ngheNghiep = new NSNgheNghiep();
        NSQuanHeGiaDinh qh = new NSQuanHeGiaDinh();
        NSDaoTao dt = new NSDaoTao();
        NSQuaTrinhCongTac qtct = new NSQuaTrinhCongTac();
        NSQuaTrinhCongTacDang qtctDang = new NSQuaTrinhCongTacDang();
        NSNhanXet nx = new NSNhanXet();
        NSKiLuat kl = new NSKiLuat();
        NSKhenThuong kt = new NSKhenThuong();
        NSNgoaiNgu nn = new NSNgoaiNgu();
        NSNgachBacLuong ngach = new NSNgachBacLuong();
        NSHopDongLaoDong hopdong = new NSHopDongLaoDong();
        NSPhuCapChucVu phuCapCV = new NSPhuCapChucVu();
        NSPhuCapKhac phuCapKhac = new NSPhuCapKhac();
        DataClassesDataContext db = new DataClassesDataContext();
        BO.CommonCode common = new Viewer.BusinessObject.BusinessEntities.CommonCode();
        BO.NSToChuc tochuc = new Viewer.BusinessObject.BusinessEntities.NSToChuc();

        public void InsertNhanVien(int nhanVienId, string type, string hotenKS, string hotenKhac, int gioiTinhId, DateTime ngaySinh, string noiSinh, int quequanID, int dantocId,
            string CMND, DateTime ngaycap
            , int noiDangKiHK, int noiOHienNayId, DateTime ngayTD, int coquanTDId,
             int trinhdoPhoThong, int heGD, string thongtinTrinhdoChuyenMon, string sotruong, string tinhtrangSK, int chieuCao, int canNang, int nhomMau,
            DateTime ngayvaoDang, DateTime ngayChinhThuc, string tochucChinhtri, DateTime ngayNhapNgu, DateTime ngayXuatNgu, int quanHam, int danhHieu, int hangThuongBinh, int conGD
            , int ngayNghiPhep, string noicap, int tongiaoId, string strAnh, string titleAnh,
            List<NSHopDongLaoDong> hdld, List<NSPhuCapChucVu> pccv, List<NSPhuCapKhac> pck, List<NSNgachBacLuong> nbl, List<NSNgoaiNgu> ngoaiNgu, List<NSKhenThuong> khenThuongObj, List<NSKiLuat> kiLuatObj, List<NSNhanXet> nhanXetObj, List<NSQuaTrinhCongTac> qtctObj, List<NSQuaTrinhCongTacDang> qtctDangObj, List<NSDaoTao> dtObj
            , string lsct, string quanHeTCNN, string quanHeNTNN, string soHieu, int cqqlId, int coquanSD, List<NSQuanHeGiaDinh> qhbtObj, List<NSQuanHeGiaDinh> qhObj,
            string soBHXH, List<NSNgheNghiep> lstNgheNghiep, int trangThai, List<NSNghiPhep> lstNghiPhep, List<NSQuaTrinhGianDoanBaoHiem> lstBaoHiem,
            DateTime ngayDongBaoHiem, bool toChucFlag, string coQuanKhac, List<NSTrangThaiLamViec> lstTrangThai, List<NSTrinhDoChuyenMon> lstTrinhDoChuyenMon, List<NSBangCap> lstBangCap, List<NSLiLuanChinhTri> lstLiLuan, List<NSQuanLiNhaNuoc> lstQLNN, List<NSTrinhDoTinHoc> lstTrinhDoTinHoc)
        {
            if (type.Equals("AddNew"))
            {
                using (var context = new DataClassesDataContext())
                {
                    NSNhanVien item = new NSNhanVien();
                    if (!(ngayDongBaoHiem.Year == 1))
                    {
                        item.NgayBatDauDongBaoHiem = ngayDongBaoHiem;
                    }
                    item.ThuocSoYTeFlag = toChucFlag;
                    item.CoQuanKhac = coQuanKhac;
                    item.SoBHXH = soBHXH;
                    item.TinhTrangSucKhoe = tinhtrangSK;
                    item.CoQuanSuDungId = coquanSD;
                    item.CoQuanQuanLiId = cqqlId;
                    item.SoHieu = soHieu;
                    item.LichSuChinhTri = lsct;
                    item.QuanHeToChucNuocNgoai = quanHeTCNN;
                    item.QuanHeNhanThanNuocNgoai = quanHeNTNN;
                    item.HoTenKhaiSinh = hotenKS;
                    item.HoTenKhac = hotenKhac;
                    item.GioiTinhID = gioiTinhId;
                    item.NgaySinh = ngaySinh;
                    item.DiaChiNoiSinh = noiSinh;
                    item.DiachiIDQueQuan = quequanID;
                    item.DanTocID = dantocId;
                    item.SoCMND = CMND;
                    item.NgayCap = ngaycap;
                    item.DiaChiIDHoKhau = noiDangKiHK;
                    item.DiaChiIDNoiOHienTai = noiOHienNayId;
                    if (!(ngayTD.Year == 1))
                    {
                        item.NgayTuyenDung = ngayTD;
                    }

                    item.ToChucIDTuyenDung = coquanTDId;
                    item.TrinhDoGiaoDucPhoThongID = trinhdoPhoThong;
                    item.HeGiaoDucPhoThongID = heGD;
                    item.ThongTinTrinhDoChuyenMon = thongtinTrinhdoChuyenMon;
                    item.SoTruongCongTac = sotruong;
                    item.NoiCap = noicap;
                    item.ChieuCao = chieuCao;
                    item.CanNang = canNang;
                    item.NhomMauID = nhomMau;
                    if (!(ngayvaoDang.Year == 1))
                    {
                        item.NgayVaoDangCSVN = ngayvaoDang;
                    }
                    if (!(ngayNhapNgu.Year == 1))
                    {
                        item.NgayNhapNgu = ngayNhapNgu;
                    }
                    if (!(ngayXuatNgu.Year == 1))
                    {
                        item.NgayXuatNgu = ngayXuatNgu;
                    }
                    if (!(ngayChinhThuc.Year == 1))
                    {
                        item.NgayChinhThucVaoDangCSVN = ngayChinhThuc;
                    }

                    item.ToChucChinhTriThamGia = tochucChinhtri;
                    item.TonGiaoID = tongiaoId;
                    item.QuanHamCaoNhatID = quanHam;
                    item.DanhHieuCaoNhatID = danhHieu;
                    item.HangThuongBinhID = hangThuongBinh;
                    item.ConGiaDinhChinhSachID = conGD;
                    item.SoNgayNghiPhepConLai = ngayNghiPhep;
                    item.Image = strAnh;
                    //Insert NhanVien
                    context.NSNhanViens.InsertOnSubmit(item);
                    context.SubmitChanges();
                    //Insert HopDongNhanVien
                    if (hdld != null)
                    {
                        foreach (var hd in hdld)
                        {
                            NSHopDongLaoDong itemHDLD = new NSHopDongLaoDong();
                            if (hd.NgayHetHieuLuc.Value.Year != 0001)
                            {
                                itemHDLD.NgayHetHieuLuc = hd.NgayHetHieuLuc;
                            }
                            itemHDLD.NgayHieuLuc = hd.NgayHieuLuc;
                            itemHDLD.HopDongLaoDongId = hd.HopDongLaoDongId;
                            itemHDLD.GhiChu = hd.GhiChu;
                            itemHDLD.NhanVienID = item.NhanVienID;
                            context.NSHopDongLaoDongs.InsertOnSubmit(itemHDLD);
                            context.SubmitChanges();
                        }
                    }
                    //Insert Phu cap chuc vu
                    if (pccv != null)
                    {
                        foreach (var pc in pccv)
                        {
                            pc.NhanVienID = item.NhanVienID;
                            context.NSPhuCapChucVus.InsertOnSubmit(pc);
                            context.SubmitChanges();
                        }

                    }
                    if (pck != null)
                    {
                        foreach (var pckItem in pck)
                        {
                            pckItem.NhanVienID = item.NhanVienID;
                            context.NSPhuCapKhacs.InsertOnSubmit(pckItem);
                            context.SubmitChanges();
                        }

                    }
                    if (lstTrangThai != null)
                    {
                        foreach (var trangThaiItem in lstTrangThai)
                        {
                            trangThaiItem.NhanVienID = item.NhanVienID;
                            context.NSTrangThaiLamViecs.InsertOnSubmit(trangThaiItem);
                            context.SubmitChanges();
                        }

                    }
                    if (lstNgheNghiep != null)
                    {
                        foreach (var ngheNghiepItem in lstNgheNghiep)
                        {
                            ngheNghiepItem.NhanVienId = item.NhanVienID;
                            context.NSNgheNghieps.InsertOnSubmit(ngheNghiepItem);
                            context.SubmitChanges();
                        }

                    }
                    if (lstQLNN != null)
                    {
                        foreach (var quanLiNNItem in lstQLNN)
                        {
                            quanLiNNItem.NhanVienID = item.NhanVienID;
                            context.NSQuanLiNhaNuocs.InsertOnSubmit(quanLiNNItem);
                            context.SubmitChanges();
                        }

                    }

                    if (lstTrinhDoTinHoc != null)
                    {
                        foreach (var trinhDoTinHocItem in lstTrinhDoTinHoc)
                        {
                            trinhDoTinHocItem.NhanVienID = item.NhanVienID;
                            context.NSTrinhDoTinHocs.InsertOnSubmit(trinhDoTinHocItem);
                            context.SubmitChanges();
                        }

                    }
                    if (lstBaoHiem != null)
                    {
                        foreach (var baoHiemItem in lstBaoHiem)
                        {
                            baoHiemItem.NhanVienID = item.NhanVienID;
                            context.NSQuaTrinhGianDoanBaoHiems.InsertOnSubmit(baoHiemItem);
                            context.SubmitChanges();
                        }

                    }
                    if (lstBangCap != null)
                    {
                        foreach (var bangCapItem in lstBangCap)
                        {
                            bangCap.NhanVienID = item.NhanVienID;
                            context.NSBangCaps.InsertOnSubmit(bangCapItem);
                            context.SubmitChanges();
                        }

                    }
                    if (lstNghiPhep != null)
                    {
                        foreach (var nghiPhepItem in lstNghiPhep)
                        {
                            nghiPhepItem.NhanVienID = item.NhanVienID;
                            context.NSNghiPheps.InsertOnSubmit(nghiPhepItem);
                            context.SubmitChanges();
                        }

                    }
                    if (lstNghiPhep != null)
                    {
                        foreach (var liLuanItem in lstLiLuan)
                        {
                            liLuanItem.NhanVienID = item.NhanVienID;
                            context.NSLiLuanChinhTris.InsertOnSubmit(liLuanItem);
                            context.SubmitChanges();
                        }

                    }
                    if (nbl != null)
                    {
                        foreach (var nblItem in nbl)
                        {
                            nblItem.NhanVienID = item.NhanVienID;
                            context.NSNgachBacLuongs.InsertOnSubmit(nblItem);
                            context.SubmitChanges();
                        }

                    }
                    if (ngoaiNgu != null)
                    {
                        foreach (var ngoaiNguItem in ngoaiNgu)
                        {
                            ngoaiNguItem.NhanVienID = item.NhanVienID;
                            context.NSNgoaiNgus.InsertOnSubmit(ngoaiNguItem);
                            context.SubmitChanges();
                        }

                    }
                    if (khenThuongObj != null)
                    {
                        foreach (var khenThuongItem in khenThuongObj)
                        {
                            khenThuongItem.NhanVienID = item.NhanVienID;
                            context.NSKhenThuongs.InsertOnSubmit(khenThuongItem);
                            context.SubmitChanges();
                        }

                    }
                    if (kiLuatObj != null)
                    {
                        foreach (var kiLuat in kiLuatObj)
                        {
                            kiLuat.NhanVienId = item.NhanVienID;
                            context.NSKiLuats.InsertOnSubmit(kiLuat);
                            context.SubmitChanges();
                        }

                    }
                    if (nhanXetObj != null)
                    {
                        foreach (var nx in nhanXetObj)
                        {
                            nx.NhanVienID = item.NhanVienID;
                            context.NSNhanXets.InsertOnSubmit(nx);
                            context.SubmitChanges();
                        }

                    }
                    if (lstTrinhDoChuyenMon != null)
                    {
                        foreach (var nx in lstTrinhDoChuyenMon)
                        {
                            nx.NhanVienID = item.NhanVienID;
                            context.NSTrinhDoChuyenMons.InsertOnSubmit(nx);
                            context.SubmitChanges();
                        }

                    }
                    //comment auto insert qtct
                    //NSQuaTrinhCongTac itemDefault = new NSQuaTrinhCongTac();
                    //itemDefault.NgayBatDau = DateTime.Now;
                    //itemDefault.CoQuanSuDungID = item.CoQuanQuanLiId;
                    //itemDefault.NoiDung = "Lần đầu tiên tuyển dụng";
                    //qtctObj.Add(itemDefault);
                    if (qtctObj != null && qtctObj.Count > 0)
                    {
                        foreach (var qt in qtctObj)
                        {
                            qt.NhanVienId = item.NhanVienID;
                            context.NSQuaTrinhCongTacs.InsertOnSubmit(qt);
                            context.SubmitChanges();
                        }
                        #region
                        //NSQuaTrinhCongTac query = (from a in qtctObj
                        //                           orderby a.NgayBatDauThayDoi descending
                        //                           select a).Take(1).SingleOrDefault();
                        //if (query != null)
                        //{
                        //    int coQuanQuanLi = query.CoQuanSuDung.HasValue ? query.CoQuanSuDung.Value : 0;
                        //    if (coQuanQuanLi > 0)
                        //    {
                        //        NSNhanVien nhanVienItem = this.GetById(item.NhanVienID);
                        //        nhanVienItem.CoQuanQuanLiId = coQuanQuanLi;
                        //        context.SubmitChanges();
                        //    }
                        //}
                        #endregion
                    }
                    if (qtctDangObj != null && qtctDangObj.Count > 0)
                    {
                        foreach (var qt in qtctDangObj)
                        {
                            qt.NhanVienID = item.NhanVienID;
                            context.NSQuaTrinhCongTacDangs.InsertOnSubmit(qt);
                            context.SubmitChanges();
                        }
                    }
                    if (dtObj != null)
                    {
                        foreach (var dt in dtObj)
                        {
                            dt.NhanVienID = item.NhanVienID;
                            context.NSDaoTaos.InsertOnSubmit(dt);
                            context.SubmitChanges();
                        }

                    }
                    if (qhbtObj != null)
                    {
                        foreach (var qhbt in qhbtObj)
                        {
                            qhbt.NhanVienID = item.NhanVienID;
                            qhbt.ModeQuanHe = 0;
                            context.NSQuanHeGiaDinhs.InsertOnSubmit(qhbt);
                            context.SubmitChanges();
                        }

                    }
                    if (qhObj != null)
                    {
                        foreach (var qhItem in qhObj)
                        {
                            qhItem.NhanVienID = item.NhanVienID;
                            qhItem.ModeQuanHe = 1;
                            context.NSQuanHeGiaDinhs.InsertOnSubmit(qhItem);
                            context.SubmitChanges();
                        }

                    }
                }
            }
            else if (type.Equals("Update"))
            {
                using (var context = new DataClassesDataContext())
                {
                    var item = context.NSNhanViens.Single(a => a.NhanVienID.Equals(nhanVienId));
                    if (!(ngayvaoDang.Year == 1))
                    {
                        item.NgayVaoDangCSVN = ngayvaoDang;
                    }
                    if (!(ngayNhapNgu.Year == 1))
                    {
                        item.NgayNhapNgu = ngayNhapNgu;
                    } if (!(ngayXuatNgu.Year == 1))
                    {
                        item.NgayXuatNgu = ngayXuatNgu;
                    }
                    if (!(ngayDongBaoHiem.Year == 1))
                    {
                        item.NgayBatDauDongBaoHiem = ngayDongBaoHiem;
                    }
                    item.ThuocSoYTeFlag = toChucFlag;
                    item.CoQuanKhac = coQuanKhac;

                    item.SoBHXH = soBHXH;
                    item.CoQuanSuDungId = coquanSD;
                    item.CoQuanQuanLiId = cqqlId;
                    item.SoHieu = soHieu;
                    item.LichSuChinhTri = lsct;
                    item.QuanHeToChucNuocNgoai = quanHeTCNN;
                    item.QuanHeNhanThanNuocNgoai = quanHeNTNN;
                    item.HoTenKhaiSinh = hotenKS;
                    item.HoTenKhac = hotenKhac;
                    item.GioiTinhID = gioiTinhId;
                    item.NgaySinh = ngaySinh;
                    item.DiaChiNoiSinh = noiSinh;
                    item.DiachiIDQueQuan = quequanID;
                    item.DanTocID = dantocId;
                    item.SoCMND = CMND;
                    item.NgayCap = ngaycap;
                    item.DiaChiIDHoKhau = noiDangKiHK;
                    item.DiaChiIDNoiOHienTai = noiOHienNayId;
                    if (!(ngayTD.Year == 1))
                    {
                        item.NgayTuyenDung = ngayTD;
                    }
                    item.ToChucIDTuyenDung = coquanTDId;

                    item.TrinhDoGiaoDucPhoThongID = trinhdoPhoThong;
                    item.HeGiaoDucPhoThongID = heGD;
                    item.ThongTinTrinhDoChuyenMon = thongtinTrinhdoChuyenMon;
                    item.SoTruongCongTac = sotruong;
                    item.NoiCap = noicap;
                    item.ChieuCao = chieuCao;
                    item.CanNang = canNang;
                    item.NhomMauID = nhomMau;

                    if (!(ngayChinhThuc.Year == 1))
                    {
                        item.NgayChinhThucVaoDangCSVN = ngayChinhThuc;
                    }

                    item.ToChucChinhTriThamGia = tochucChinhtri;

                    item.TonGiaoID = tongiaoId;
                    item.QuanHamCaoNhatID = quanHam;
                    item.DanhHieuCaoNhatID = danhHieu;
                    item.HangThuongBinhID = hangThuongBinh;
                    item.ConGiaDinhChinhSachID = conGD;

                    item.TinhTrangSucKhoe = tinhtrangSK;
                    item.Image = strAnh;
                    item.SoNgayNghiPhepConLai = ngayNghiPhep;

                    context.SubmitChanges();
                    if (pccv != null)
                    {
                        phuCapCV.UpdatePCCV_NhanVien(nhanVienId, pccv);
                    }
                    if (pck != null)
                    {
                        phuCapKhac.UpdatePCK_NhanVien(nhanVienId, pck);
                    }
                    if (hdld != null)
                    {
                        hopdong.UpdateHDLD_NhanVien(nhanVienId, hdld);
                    }
                    if (lstBaoHiem != null)
                    {
                        baoHiem.UpdateQTGD_NhanVien(nhanVienId, lstBaoHiem);
                    }
                    if (nbl != null)
                    {
                        ngach.UpdateNBL_NhanVien(nhanVienId, nbl);
                    }
                    if (lstTrangThai != null)
                    {
                        trangThaiLamViec.UpdateTrangThaiLamViec_NhanVien(nhanVienId, lstTrangThai);
                    }
                    if (lstNghiPhep != null)
                    {
                        nghiPhep.UpdateNghiPhep_NhanVien(nhanVienId, lstNghiPhep);
                    }
                    if (lstLiLuan != null)
                    {
                        liLuan.Update(nhanVienId, lstLiLuan);
                    }
                    if (lstTrinhDoTinHoc != null)
                    {
                        trinhDoTinHoc.Update(nhanVienId, lstTrinhDoTinHoc);
                    }
                    if (lstQLNN != null)
                    {
                        quanLiNN.Update(nhanVienId, lstQLNN);
                    }
                    if (ngoaiNgu != null)
                    {
                        nn.UpdateNgoaiNgu_NhanVien(nhanVienId, ngoaiNgu);
                    }
                    if (khenThuongObj != null)
                    {
                        kt.UpdateKT_NhanVien(nhanVienId, khenThuongObj);
                    }
                    if (lstNgheNghiep != null)
                    {
                        ngheNghiep.UpdateNgheNghiep_NhanVien(nhanVienId, lstNgheNghiep);
                    }
                    if (lstTrinhDoChuyenMon != null)
                    {
                        trinhDoChuyenMonItem.UpdateTDCM_NhanVien(nhanVienId, lstTrinhDoChuyenMon);
                    }
                    if (lstBangCap != null)
                    {
                        bangCap.Update(nhanVienId, lstBangCap);
                    }

                    if (kiLuatObj != null)
                    {
                        kl.UpdateKL_NhanVien(nhanVienId, kiLuatObj);
                    }
                    if (nhanXetObj != null)
                    {
                        nx.UpdateNX_NhanVien(nhanVienId, nhanXetObj);
                    }
                    if (qtctObj != null && qtctObj.Count > 0)
                    {
                        qtct.UpdateQTCT_NhanVien(nhanVienId, qtctObj);
                        //NSQuaTrinhCongTac query = (from a in qtctObj
                        //                           orderby a.NgayBatDau descending
                        //                           select a).Take(1).SingleOrDefault();
                        //if (query != null)
                        //{
                        //    int coQuanQuanLi = query.CoQuanSuDungID.HasValue ? query.CoQuanSuDungID.Value : 0;
                        //    if (coQuanQuanLi > 0)
                        //    {
                        //        var nhanVienItem = context.NSNhanViens.Single(a => a.NhanVienID.Equals(item.NhanVienID));
                        //        nhanVienItem.CoQuanQuanLiId = coQuanQuanLi;
                        //        context.SubmitChanges();
                        //    }
                        //}
                    }

                    if (qtctDangObj != null && qtctDangObj.Count > 0)
                    {
                        qtctDang.UpdateQTCTDang_NhanVien(nhanVienId, qtctDangObj);
                    }
                    if (dtObj != null)
                    {
                        dt.UpdateDT_NhanVien(nhanVienId, dtObj);
                    }
                    if (qhbtObj != null)
                    {
                        qh.UpdateQH_NhanVien(nhanVienId, qhbtObj, "QHBT");
                    }
                    if (qhObj != null)
                    {
                        qh.UpdateQH_NhanVien(nhanVienId, qhObj, "QH");
                    }
                }
            }
        }
        public IEnumerable LoadAllGrid()
        {

            var query = from a in db.NSNhanViens.AsEnumerable()
                        select new
                        {
                            Id = a.NhanVienID,
                            Ten = a.HoTenKhaiSinh,
                            SoHieu = a.SoHieu,
                            NgaySinh = a.NgaySinh.ToString("dd/MM/yyyy"),
                            ChucVuHienTai = string.Empty,
                            QuanHam = common.LoadCommonCodeById(a.QuanHamCaoNhatID.Value),
                            QueQuan = tochuc.LayDiaChi(a.DiachiIDQueQuan),
                            SoCMND = a.SoCMND,
                            CoQuanQL = tochuc.LayTenToChuc(a.CoQuanQuanLiId.Value)

                        };
            return query.ToList();
        }
        public NSNhanVien LoadNhanVienById(int id)
        {
            var query = from a in db.NSNhanViens
                        where a.NhanVienID.Equals(id)
                        select a;
            return query.SingleOrDefault();
        }
        public void DeleteNhanVien(int id)
        {
            //xóa dữ liệu các bảng con theo nhân viên.
            hopdong.DeleletAllHDLD(id);
            phuCapCV.DeleletAllPCCVByNhanVien(id);
            phuCapKhac.DeleletAllPCKByNhanVien(id);
            //Xóa nhân viên
            using (var context = new DataClassesDataContext())
            {
                var item = context.NSNhanViens.Single(a => a.NhanVienID.Equals(id));
                context.NSNhanViens.DeleteOnSubmit(item);
                context.SubmitChanges();
            }

        }

        public string GetTenTrangThaiLamViec(int nhanVienID)
        {
            NSTrangThaiLamViec trangThai = new BO.NSTrangThaiLamViec().GetTrangThaiLamViecMoiNhatByNhanVienID(nhanVienID);
            if (trangThai != null)
            {
                return trangThai.TenTrangThai;
            }
            else return string.Empty;
        }

        public string GetChucVu(int nhanVienID)
        {
            NSQuaTrinhCongTac chucvu = new BO.NSQuaTrinhCongTac().GetQuaTrinhCongTacMoiNhatByNhanVienID(nhanVienID);
            if (chucvu != null)
            {
                return chucvu.ChucVu;
            }
            else return string.Empty;
        }

        public string GetNgheNghiep(int nhanVienID)
        {
            NSNgheNghiep ngheNghiep = new BO.NSNgheNghiep().GetNgheNghiepMoiNhatByNhanVienID(nhanVienID);
            if (ngheNghiep != null)
            {
                CHCommonCode codeNghe = common.LoadCommonCodeByCodeId(ngheNghiep.CodeIdNgheNghiep != null ? (int)ngheNghiep.CodeIdNgheNghiep : 0);
                return codeNghe != null ? codeNghe.CodeValue : string.Empty;
            }
            else return string.Empty;
        }

        public IEnumerable LoadAllGridByToChucId(int idToChuc)
        {
            List<int> dsToChucTheoUser = new BO.NSToChuc().GetToChucTheoUserID(Viewer.BusinessObject.BusinessEntities.QUNguoiDung.GetByUserName(HttpContext.Current.User.Identity.Name).NguoiDungID);
            DataClassesDataContext db = new DataClassesDataContext();
            if (HttpContext.Current.User.Identity.Name.ToLower() != "admin")
            {
                var query = from a in db.NSNhanViens.AsEnumerable()

                            where (idToChuc > 0 ? a.CoQuanQuanLiId.Value.Equals(idToChuc) : true)
                            && dsToChucTheoUser.Contains((int)a.CoQuanQuanLiId)
                            select new
                            {
                                Id = a.NhanVienID,
                                Ten = a.HoTenKhaiSinh,
                                SoHieu = a.SoHieu,
                                NgaySinh = a.NgaySinh.ToString("dd/MM/yyyy"),
                                QuanHam = common.LoadCommonCodeById(a.QuanHamCaoNhatID.Value),
                                QueQuan = tochuc.LayDiaChi(a.DiachiIDQueQuan),
                                SoCMND = a.SoCMND,
                                CoQuanQL = tochuc.LayTenToChuc(a.CoQuanQuanLiId.Value),
                                TinhTrangCongTac = GetTenTrangThaiLamViec(a.NhanVienID),
                                ChucVuHienTai = GetChucVu(a.NhanVienID),
                                NgheNghiep = GetNgheNghiep(a.NhanVienID)
                            };
                return query.ToList();
            }
            else
            {
                var query = from a in db.NSNhanViens.AsEnumerable()
                            where (idToChuc > 0 ? a.CoQuanQuanLiId.Value.Equals(idToChuc) : true)
                            select new
                            {
                                Id = a.NhanVienID,
                                Ten = a.HoTenKhaiSinh,
                                SoHieu = a.SoHieu,
                                NgaySinh = a.NgaySinh.ToString("dd/MM/yyyy"),
                                QuanHam = common.LoadCommonCodeById(a.QuanHamCaoNhatID.Value),
                                QueQuan = tochuc.LayDiaChi(a.DiachiIDQueQuan),
                                SoCMND = a.SoCMND,
                                CoQuanQL = tochuc.LayTenToChuc(a.CoQuanQuanLiId.Value),
                                TinhTrangCongTac = GetTenTrangThaiLamViec(a.NhanVienID),
                                ChucVuHienTai = GetChucVu(a.NhanVienID),
                                NgheNghiep = GetNgheNghiep(a.NhanVienID)
                            };
                return query.ToList();
            }
        }
        int GetTotalDay(DateTime d1, DateTime now)
        {
            TimeSpan t = now - d1;
            double NrOfDays = Math.Round(t.TotalDays, 0);
            return Convert.ToInt32(NrOfDays);
        }
        public IEnumerable SearchByCondition(int idToChuc, string hoten, int trinhdoId, int liluanId, string toanTuCongTac, int namCongTac, string toanTuVaoDang, int namVaoDang, int diachiId, bool checkNghiHuu, string sohieuCBCC, string soCMND)
        {
            DataClassesDataContext db = new DataClassesDataContext();
            var query = from a in db.NSNhanViens.AsEnumerable()
                        where (idToChuc > 0 ? a.CoQuanQuanLiId.Value.Equals(idToChuc) : true)
                        select new
                        {
                            Id = a.NhanVienID,
                            SoHieu = a.SoHieu,
                            Ten = a.HoTenKhaiSinh,
                            NgaySinh = a.NgaySinh.ToString("dd/MM/yyyy"),
                            QuanHam = a.QuanHamCaoNhatID.HasValue ? common.LoadCommonCodeById(a.QuanHamCaoNhatID.Value) : string.Empty,
                            QueQuan = tochuc.LayDiaChi(a.DiachiIDQueQuan),
                            SoCMND = a.SoCMND,
                            //CoQuanQL = a.CoQuanQuanLiId.HasValue ? tochuc.LayTenToChuc(a.CoQuanQuanLiId.Value) : string.Empty,
                            ThamNienCongTac = a.NgayTuyenDung.HasValue ? GetTotalDay(a.NgayTuyenDung.Value, DateTime.Now) : 0,
                            ThamNienVaoDang = a.NgayChinhThucVaoDangCSVN.HasValue ? GetTotalDay(a.NgayChinhThucVaoDangCSVN.Value, DateTime.Now) : 0,
                            CoquanSuDung = a.CoQuanSuDungId.Value,
                            DiaChiIDNoiOHienTai = a.DiaChiIDNoiOHienTai,
                            TrangThaiLamViec = a.TrangThaiLamViec
                        };
          
            if (!string.IsNullOrEmpty(soCMND.Trim()))
            {
                query = query.Where(a => a.SoCMND.ToLower().Contains(soCMND.ToLower()));
            }

            if (!string.IsNullOrEmpty(sohieuCBCC.Trim()))
            {
                query = query.Where(a => a.SoHieu.ToLower().Contains(sohieuCBCC.ToLower()));
            }

            if (namCongTac > 0)
            {
                if (toanTuCongTac.Equals(">"))
                {
                    query = query.Where(b => b.ThamNienCongTac > namCongTac * 365);
                }
                else if (toanTuCongTac.Equals("<"))
                {
                    query = query.Where(b => b.ThamNienCongTac < namCongTac * 365);
                }
                else if (toanTuCongTac.Equals("="))
                {
                    query = query.Where(b => b.ThamNienCongTac.Equals(namCongTac * 365));
                }
            }
            if (namVaoDang > 0)
            {
                if (toanTuCongTac.Equals(">"))
                {
                    query = query.Where(b => b.ThamNienVaoDang > namVaoDang * 365);
                }
                else if (toanTuCongTac.Equals("<"))
                {
                    query = query.Where(b => b.ThamNienVaoDang < namVaoDang * 365);
                }
                else if (toanTuCongTac.Equals("="))
                {
                    query = query.Where(b => b.ThamNienVaoDang.Equals(namVaoDang * 365));
                }
            }
         
           
            if (diachiId > 0)
            {
                query = query.Where(a => a.DiaChiIDNoiOHienTai.Equals(diachiId));
            }
            if (checkNghiHuu.Equals(false))
            {
                query = query.Where(a => a.TrangThaiLamViec.HasValue ? a.TrangThaiLamViec.Value.Equals(1568) : true);
            }
            return query.ToList();
            }

        public NSNhanVien GetById(int pNhanVienId)
        {
            return this.Context.NSNhanViens.Where(c => c.NhanVienID == pNhanVienId).FirstOrDefault();
        }

        #region Additional Properties
        private CHDiaChi _diachiHoKhau;
        public CHDiaChi DiaChiHoKhau
        {
            get
            {
                if (_diachiHoKhau == null)
                {
                    if (this.DiaChiIDHoKhau == 0)
                    {
                        return new CHDiaChi();
                    }
                    _diachiHoKhau = this.Context.CHDiaChis.Where(c => c.DiaChiID == this.DiaChiIDHoKhau).FirstOrDefault();
                }
                return _diachiHoKhau;
            }
        }

        private CHDiaChi _diachiHienTai;
        public CHDiaChi DiaChiHienTai
        {
            get
            {
                if (_diachiHienTai == null)
                {
                    if (this.DiaChiIDNoiOHienTai == 0)
                    {
                        return new CHDiaChi();
                    }
                    _diachiHienTai = this.Context.CHDiaChis.Where(c => c.DiaChiID == this.DiaChiIDNoiOHienTai).FirstOrDefault();
                }
                return _diachiHienTai;
            }
        }
        #endregion
        public void InsertHDLD(int nhanVienId, DateTime ngayHieuLuc, DateTime ngayHetHieuLuc, string ghiChu)
        {
            using (var db = new DataClassesDataContext())
            {
                NSHopDongLaoDong hd = new NSHopDongLaoDong();
                hd.NhanVienID = nhanVienId;
                hd.NgayHieuLuc = ngayHieuLuc;
                hd.NgayHetHieuLuc = ngayHetHieuLuc;
                hd.GhiChu = ghiChu;
                db.NSHopDongLaoDongs.InsertOnSubmit(hd);
                db.SubmitChanges();
            }
        }
        public NSHopDongLaoDong GetHDLD(DateTime ngayHieuLuc, DateTime ngayHetHieuLuc, string ghiChu)
        {
            var db = new DataClassesDataContext();
            NSHopDongLaoDong hd = new NSHopDongLaoDong();

            hd.NgayHieuLuc = ngayHieuLuc;
            hd.NgayHetHieuLuc = ngayHetHieuLuc;
            hd.GhiChu = ghiChu;
            return hd;
        }
        public NSPhuCapChucVu LoadPCCVByPhuCapId(int hdId)
        {
            var query = (from a in db.NSPhuCapChucVus
                         where a.PhuCapId.Equals(hdId)
                         select a).SingleOrDefault();
            return query;

        }
        public NSPhuCapChucVu GetPhuCapChucVu(int nhanVienId, decimal heSo, int loaiPhuCapId, DateTime ngayHieuLuc, DateTime ngayHetHieuLuc, string ghiChu, string tenPhuCap)
        {
            var db = new DataClassesDataContext();
            NSPhuCapChucVu hd = new NSPhuCapChucVu();
            hd.PhuCapId = 0 - DateTime.Now.Second;
            hd.NgayHieuLuc = ngayHieuLuc;
            hd.NgayHetHieuLuc = ngayHetHieuLuc;
            hd.GhiChu = ghiChu;
            hd.LoaiPhuCapChucVuID = loaiPhuCapId;
            hd.HeSoPhuCap = heSo;
            hd.LoaiPhuCap = tenPhuCap;
            hd.NhanVienID = nhanVienId;
            return hd;
        }
        public void InsertPCCV(int nhanVienId, decimal heSo, int loaiPhuCap, DateTime ngayHieuLuc, DateTime ngayHetHieuLuc, string ghiChu)
        {
            using (var db = new DataClassesDataContext())
            {
                NSPhuCapChucVu hd = new NSPhuCapChucVu();
                hd.NhanVienID = nhanVienId;
                hd.NgayHieuLuc = ngayHieuLuc;
                hd.NgayHetHieuLuc = ngayHetHieuLuc;
                hd.GhiChu = ghiChu;
                hd.LoaiPhuCapChucVuID = loaiPhuCap;
                hd.HeSoPhuCap = heSo;
                db.NSPhuCapChucVus.InsertOnSubmit(hd);
                db.SubmitChanges();
            }
        }


        public NSPhuCapKhac LoadPCKByPhuCapId(int hdId)
        {
            var query = (from a in db.NSPhuCapKhacs
                         where a.PhuCapKhacId.Equals(hdId)
                         select a).SingleOrDefault();
            return query;

        }
        public List<object> ReturnMonthCount(int nodeToChucId, int month)
        {

            List<object> result = new List<object>();
            DataClassesDataContext db = new DataClassesDataContext();
            var query = from a in db.NSNhanViens.AsEnumerable()
                        where (nodeToChucId > 0 ? a.CoQuanQuanLiId.Value.Equals(nodeToChucId) : true)
                        select new
                        {
                            Id = a.NhanVienID,
                            Ten = a.HoTenKhaiSinh,
                            SoHieu = a.SoHieu,
                            NgaySinhGoc = a.NgaySinh,
                            NgaySinh = a.NgaySinh.ToString("dd/MM/yyyy"),
                            ChucVuHienTai = GetChucVuNhanVien(a.NhanVienID),
                            QuanHam = common.LoadCommonCodeById(a.QuanHamCaoNhatID.Value),
                            QueQuan = tochuc.LayDiaChi(a.DiachiIDQueQuan),
                            SoCMND = a.SoCMND,
                            CoQuanQL = tochuc.LayTenToChuc(a.CoQuanQuanLiId.Value),
                            GioiTinh = common.LoadCommonCodeById(a.GioiTinhID),
                            NgayDuDinhVeHuu = GetNgayDuDinhVeHuu(a.NgaySinh, common.LoadCommonCodeById(a.GioiTinhID)),
                            SoThangDuDinhConLai = ReturnMonthConLai(GetNgayDuDinhVeHuu(a.NgaySinh, common.LoadCommonCodeById(a.GioiTinhID)))
                        };
            foreach (var item in query)
            {
                if ((item.GioiTinh.Equals("Nam") && ReturnMonth(item.NgaySinhGoc) < 720 && (ReturnMonth(item.NgaySinhGoc) + month) > 720) || (item.GioiTinh.Equals("Nữ") && ReturnMonth(item.NgaySinhGoc) < 660 && (ReturnMonth(item.NgaySinhGoc) + month) > 660))
                {
                    result.Add(item);
                }
            }
            return result;

        }

        private DateTime GetNgayDuDinhTangLuong(int nhanVienId)
        {

            var query = (from a in db.NSNgachBacLuongs
                         where a.NhanVienID.Equals(nhanVienId)
                         orderby a.NgayHieuLuc descending
                         select a).Take(1).SingleOrDefault();
            if (query != null)
            {
                return (DateTime)query.NgayHieuLuc;
            }
            return DateTime.Now;
        }
        private DateTime GetNgayDuDinhVeHuu(DateTime ngaySinh, string gioiTinh)
        {

            if (gioiTinh.Equals("Nam"))
            {

                return ngaySinh.AddYears(60);
            }

            return ngaySinh.AddYears(55);

        }
        private string GetChucVuNhanVien(int nhanVienId)
        {

            var query = (from a in db.NSQuaTrinhCongTacs
                         where a.NhanVienId.Equals(nhanVienId)
                         orderby a.NgayBatDau descending
                         select a).Take(1).SingleOrDefault();
            if (query != null)
            {
                return query.ChucVu;
            }
            return string.Empty;
        }
        public List<object> ReturnMonthCountForTangLuong(int nodeToChucId, int month)
        {

            List<object> result = new List<object>();
            DataClassesDataContext db = new DataClassesDataContext();
            var query = from a in db.NSNhanViens.AsEnumerable()
                        where
                        (nodeToChucId > 0 ? a.CoQuanQuanLiId.Value.Equals(nodeToChucId) : true)

                        select new
                        {
                            Id = a.NhanVienID,
                            Ten = a.HoTenKhaiSinh,
                            SoHieu = a.SoHieu,
                            NgaySinhGoc = a.NgaySinh,
                            NgaySinh = a.NgaySinh.ToString("dd/MM/yyyy"),
                            ChucVuHienTai = string.Empty,
                            QuanHam = common.LoadCommonCodeById(a.QuanHamCaoNhatID.Value),
                            QueQuan = tochuc.LayDiaChi(a.DiachiIDQueQuan),
                            SoCMND = a.SoCMND,
                            CoQuanQL = tochuc.LayTenToChuc(a.CoQuanQuanLiId.Value),
                            GioiTinh = common.LoadCommonCodeById(a.GioiTinhID),
                            NgayDuDinhTangLuong = GetNgayDuDinhTangLuong(a.NhanVienID),
                            ThoiGianConLai = ReturnMonthConLai(GetNgayDuDinhTangLuong(a.NhanVienID))
                        };
            foreach (var item in query)
            {

                DateTime tmpDate = DateTime.Parse(item.NgayDuDinhTangLuong.ToString());
                if (ReturnMonthConLai(tmpDate) < 36 && (ReturnMonthConLai(tmpDate) + month) > 36)
                {
                    result.Add(item);
                }
            }
            return result;

        }

        private int ReturnMonth(DateTime ngaySinh)
        {
            double monthCount = 0;
            DateTime now = DateTime.Now;
            TimeSpan result = now.Subtract(ngaySinh);
            double t = result.Days;
            monthCount = Math.Floor(t / 30.475);
            return Convert.ToInt32(Math.Round(monthCount, 0));
        }
        private int ReturnMonthConLai(DateTime ngayDuDinh)
        {
            double monthCount = 0;
            DateTime now = DateTime.Now;
            TimeSpan result = ngayDuDinh.Subtract(now);
            double t = result.Days;
            monthCount = Math.Floor(t / 30.475);
            return Convert.ToInt32(Math.Round(monthCount, 0));
        }

    }

}
